package com.xhh.nphm.system.mapper;

import com.xhh.nphm.common.core.domain.QueryConditionVo;
import com.xhh.nphm.common.core.domain.entity.Dept;
import com.xhh.nphm.system.domain.*;
import org.apache.ibatis.annotations.Lang;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;
import org.harmony.toddler.mybatis.groovy.GroovyLangDriver;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
public interface CommonDao {

    /**
     * 查询地区
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='pcArea' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findArea();

    /**
     * 查询用户类别
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='pcUserSort' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findUserType();

    /**
     * 查询交办意见，坐席处理
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='opinion' AND useFlag=1  AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findOpinion();

    /**
     * 查询坐席备注
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='SeatsRemark' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findSeatsRemark();

    /**
     * 查询证件类型
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='bcCerType' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findCerType();

    /**
     * 查询来电类别
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='bcTelSort' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findTelType();

    /**
     * 查询工单性质
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall "
            + " WHERE codeBig='pcTransact' AND useFlag=1 AND CSValue>0 AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findWorkFormType();

    /**
     * 查询办理方式
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall " +
            " where CodeBig='bcWfDealWay' and CSValue<>'-2' and IsDel=0 order by CSOrd")
    List<Map> findDealWay();

    /**
     * 根据id查询内容分类
     *
     * @return
     */
    @Select("select CodePK,SortName,CodeParent from bSort where CodePK=#{cid}")
    BSort findContentCategoryById(String cid);

    /**
     * 查询内容分类
     *
     * @return
     */
    @Select("select CodePK,SortName,CodeParent,EndFlag from bSort where isDel=0 and UseFlag=1 and CodePK>11 ORDER BY CodeParent,OrdNum")
    List<Map> findContentCategories();

    /**
     * 办理期限（默认延期5天）
     *
     * @param date yyyy-MM-dd
     * @return
     */
    @Select("select top 1 RtnStr from\n" +
            "(select top 5 PkId,RtnStr from bWorkDay where PkId>(select PkId from [bWorkDay] where NatureDay=#{date})\n" +
            "and IsWork=1) k order by PkId desc")
    String nextXWorkdayGet(@Param("date") String date);


    /**
     * 办理期限(规定延期天数)
     *
     * @param day 延期天数
     * @param date yyyy-MM-dd
     * @return
     */
    @Select("select top 1 RtnStr from " +
            "(select top ${day} PkId,RtnStr from bWorkDay where PkId>(select PkId from [bWorkDay] where NatureDay=#{date})\n" +
            "and IsWork=1) k order by PkId desc")
    String myNextXWorkdayGet(@Param("day") int day, @Param("date") String date);

    /**
     * 查询延期理由
     *
     * @return
     */
    @Select("select CSValue,CSText\n" +
            "from ScodeSmall WHERE codeBig='bcDelayRsn' AND useFlag=1 AND csvalue<>'-2' AND isDel=0 ORDER BY csord")
    List<Map> delayReasonList();

    /**
     * 一级部门
     * select NoUnit,DeptShort from bUserDept where AreaNo=5101 and len(NoUnit)=len(304) and UseState=1 and IsDel=0 order by OrdNum
     *
     * @return
     */
    @Select("select noUnit,deptShort,noParent from bUserDept where AreaNo=5101 and len(NoUnit)=len(304) and UseState=1 and IsDel=0 order by OrdNum")
    List<BuserDept> firstDeptList();

    /**
     * 二级部门
     * select NoUnit,DeptShort from bUserDept where NoParent=304 and UseState=1 and IsDel=0 order by OrdNum
     *
     * @return
     */
    @Select("select noUnit,deptShort,noParent from bUserDept where NoParent=304 and UseState=1 and IsDel=0 order by OrdNum")
    List<BuserDept> secondDeptList();

    /**
     * 三级部门
     * select NoUnit,DeptShort from bUserDept where AreaNo=510104 and len(NoUnit)=len(301040) and UseState=1 and IsDel=0 order by OrdNum
     *
     * @return
     */
    @Select("select noUnit,deptShort,noParent from bUserDept where AreaNo=#{areaNo} and len(NoUnit)=len(#{noUnit}) and UseState=1 and IsDel=0 order by OrdNum")
    List<BuserDept> thirdDeptList(String areaNo, String noUnit);

    /**
     * 三级街道办
     * select NoUnit,DeptShort from bUserDept where NoParent=301040 and UseState=1 and IsDel=0 order by OrdNum
     *
     * @return
     */
    @Select("select noUnit,deptShort,noParent from bUserDept where AreaNo=#{areaNo} and NoParent=#{noUnit} and UseState=1 and IsDel=0 order by OrdNum")
    List<BuserDept> thirdStreetList(String areaNo, String noUnit);

    /**
     * 四级社区
     * select * from bUserDept where AreaNo=510109 and UseState=1 and IsDel=0 and len(NoParent)>len(301090)
     *
     * @return
     */
    @Select("select noUnit,deptShort,noParent from bUserDept where AreaNo=#{areaNo} and len(NoParent)>len(#{noUnit}) and UseState=1 and IsDel=0 order by OrdNum")
    List<BuserDept> fourthCommunityList(String areaNo, String noUnit);

    /**
     * 分页查询
     *
     * @param page
     * @param rows
     * @param fieldList
     * @param tabName
     * @param pkName
     * @param strWhere
     * @param strOrder
     * @return
     */
    @Select("exec ProcQueryByPage @iPageNo=#{page},@iPageSize=#{rows},@fieldList=#{fieldList},@tabName=#{tabName},@pkName=#{pkName},@strWhere=#{strWhere},@strOrder=#{strOrder},@iRecCount=#{recCount,jdbcType=NUMERIC,mode=OUT}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map<String, Object>> procQueryPage(Map<String, Object> params);

    /**
     * 查询内容分类
     *
     * @return
     */
    @Select("select CodePK id,SortName text,CodeParent pid,case when EndFlag=0 then 'closed' else 'open' end as state from bSort where isDel=0 and UseFlag=1 and CodePK>11 ORDER BY CodeParent,OrdNum")
//    @Select("select CodePK id,SortName text,CodeParent pid,case when EndFlag=0 then 'closed' else 'open' end as state from bSort where CodePK=1101 or (CodeParent=1101 and CodePK in(110101)) or CodePK=11010101 ORDER BY CodeParent,OrdNum")
//    @Select("select CodePK id,SortName text,CodeParent pid,case when EndFlag=0 then 'closed' else 'open' end as state from bSort where CodePK=1101 or (CodeParent=1101 and CodePK in(110101)) or CodeParent=110101 ORDER BY CodeParent,OrdNum")
    List<NodeCate> findContentCate();

    /**
     * 查询本级部门
     * SELECT noUnit,deptShort FROM bUserDept WHERE areaNo=5101 AND LEN(noUnit)=LEN(304) AND useState=1 AND isDel=0 ORDER BY ordNum
     *
     * @param fpArea 5101
     * @param deptNo 304
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE areaNo=#{fpArea} AND LEN(noUnit)=LEN(#{deptNo}) AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<Map> queryFlatDept(@Param("fpArea") String fpArea, @Param("deptNo") String deptNo);

    @Select("SELECT deptShort FROM bUserDept WHERE areaNo=#{fpArea} AND LEN(noUnit)=LEN(#{deptNo}) AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<String> findFlatDept(@Param("fpArea") String fpArea, @Param("deptNo") String deptNo);

    /**
     * 查询本级部门 四级 职能部门 锦江区市场监督管理局
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 and grade=1 ORDER BY ordNum")
    List<Map> queryFlatDeptFourthZhiNeng(@Param("deptNo") String deptNo);


    /**
     * 查询四级部门
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 and layerNum=4 ORDER BY ordNum")
    List<Map> queryFlatDeptFourth(@Param("deptNo") String deptNo);

    /**
     * 查询四级部门
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 and layerNum=4 and grade=#{grade} ORDER BY ordNum")
    List<Map> queryFlatDeptFourthGrade(@Param("deptNo") String deptNo, @Param("grade") Integer grade);

    /**
     * 查询五级部门
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 and layerNum=5 ORDER BY ordNum")
    List<Map> queryFlatDeptFifth(@Param("deptNo") String deptNo);

    /**
     * 查询五级部门
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 and layerNum=5 and grade=#{grade} ORDER BY ordNum")
    List<Map> queryFlatDeptFifthGrade(@Param("deptNo") String deptNo, @Param("grade") Integer grade);

    /**
     * 查询下级部门
     * 区(市)县
     * SELECT noUnit,deptShort FROM bUserDept WHERE noParent='304' AND useState=1 AND isDel=0 ORDER BY ordNum
     *
     * @param fpParent
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{fpParent} AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<Map> querySonDept(@Param("fpParent") String fpParent);

    @Select("SELECT deptShort FROM bUserDept WHERE noParent=#{fpParent} AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<String> findSonDept(@Param("fpParent") String fpParent);

    /**
     * @Description: 递归查询部门树
     * @Param: [deptId]
     * @return: java.util.List<java.util.Map>
     */
    @Select(" with deptTree as ( " +
            " select noUnit,deptShort from bUserDept where pno=#{deptId} and isDel=0 and useState=1 union all " +
            " select a.noUnit,a.deptShort from bUserDept a, deptTree b where a.pno = b.NoUnit and a.isDel=0 and a.useState=1) " +
            " select noUnit,deptShort from deptTree")
    List<Map> findAllDept(@Param("deptId")String deptId);

    /**
     * 查询下级部门 四级部门辖区
     * 区(市)县
     *
     * @param deptNo
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 And grade=2 ORDER BY ordNum")
    List<Map> querySonDeptFourthXiaQu(@Param("deptNo") String deptNo);

    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent=#{deptNo} AND useState=1 AND isDel=0 And layerNum=5 ORDER BY ordNum")
    List<Map> querySonDeptCommunity(@Param("deptNo") String deptNo);


    /**
     * 拟办期限 查询生成
     * @param fpYear
     * @param fpMonth
     * @param fpDay
     * @param fpStep
     * @return
     */
    @Select("{call pNextWorkdayGet(#{fpYear,mode=IN},#{fpMonth,mode=IN},#{fpDay,mode=IN},#{fpStep,mode=IN})}")
    Map<String, Object> getNextXWorkday(@Param("fpYear") int fpYear, @Param("fpMonth") int fpMonth, @Param("fpDay") int fpDay, @Param("fpStep") int fpStep);

    /**
     * 查询系统公告类别
     *
     * @return
     */
    @Select("select CSValue,CSText from sCodeSmall where CodeBig='bcNtcType' and UseFlag=1 and CSValue<>'-2' and IsDel=0 order by CSOrd")
    List<Map> findNtcTypes();

    /**
     * 查询敏感信息属性
     * @return
     */
    @Select("SELECT id,name,code FROM tm_sensitive_info_field where isDel = 0")
    List<SensitiveInfoField> findSensitiveField();

    /**
     * 查询承办单位 对应编号
     * @param sbdeptName
     * @return
     */
    @Select("SELECT noUnit FROM bUserDept WHERE DeptShort=#{sbdeptName}  AND useState=1 AND isDel=0 ORDER BY ordNum")
    String selectDeptShort(@Param("sbdeptName") String sbdeptName);

    /**
     * 查询信件处理意见
     *
     * @return
     */
    @Select("select CSValue,CSText,Remark from sCodeSmall "
            + " WHERE codeBig='opinionM' AND useFlag=1  AND isDel=0 "
            + " ORDER BY CSOrd")
    List<Map> findOpinionM();

    /**
     * 获取未被占用的分机号
     * @return
     */
    @Select("SELECT TelNo FROM bUserTelNo WHERE UseState = 0 and IsDel = 0")
    List<String> fetchRandomTklTNo();

    /**
     * 查询重要代办事项
     * @return 列表
     */
    @Select("{call sp_first_wait_test(#{roleId,mode=IN,jdbcType=VARCHAR},#{deptId,mode=IN,jdbcType=VARCHAR})}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map> psPgFstWait(Map<String, String> params);

    /**
     * 查询重要代办事项
     * @return 列表
     */
    @Select("{call sp_first_wait_test_gxq(#{roleId,mode=IN,jdbcType=VARCHAR},#{deptId,mode=IN,jdbcType=VARCHAR})}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map> psPgFstWait_gxq(Map<String, String> params);

    @Select("QueryReportSql#mailWFDeptList")
    @Lang(GroovyLangDriver.class)
    @Options(statementType = StatementType.PREPARED)
    List<Map> mailWFDeptListJson(Map paramMap);

    @Select("SELECT JudgeC FROM ( SELECT ROW_NUMBER()over(order by pkid desc) AS groupIndex,* from mWfJudge where WfId = #{WfId}) AS res WHERE res.groupIndex = 1 ")
    Map<String,Object> findMailJudgeByWfId(@Param("WfId") String wfId);

    /**
     * 查询信件操作记录
     * @param queryConditionVo
     * @return
     */
    @Select({"<script>" +
            "select LogId as myPkVal,WfId,LogTime as opTime,SpareString,FlagString,OpDeptNo,OpDeptName,OpUserName,OpAction,OpDesc,WsOpen,WsDesc,CStepId,NStepId " +
            "from mMailWorkLog m1 " +
            " where IsDel=0 and OpDeptNo=#{deptNo} and (LogTime between #{mail.beginTime} and  #{mail.endTime })" +
            " <if test='mail.opUserName!=\"\" or mail.sbDeptName!=\"\"'>" +
            "  <if test='mail.opUserName!=\"\"' > " +
            "   and OpUserName =#{mail.opUserName} " +
            "  </if>" +
            "  <if test='mail.sbDeptName!=\"\"' >" +
            "   and  OpDeptName =#{mail.sbDeptName}" +
            "  </if>" +
            " </if>" +
            "</script>" })
    List<Map> mailLogList(@Param("deptNo")String deptNo,@Param("mail") QueryConditionVo queryConditionVo);

    /**
     * 信件操作记录导出
     * @param queryConditionVo
     * @return
     */
    @Select({"<script>" +
            "select LogId as myPkVal,WfId,LogTime as opTime,SpareString,FlagString,OpDeptNo,OpDeptName,OpUserName,OpAction,OpDesc,WsOpen,WsDesc,CStepId,NStepId " +
            "from mMailWorkLog m1 " +
            "where IsDel=0  and OpDeptNo=#{deptNo} and (LogTime between #{mail.beginTime} and  #{mail.endTime}) " +
            " <if test='mail.opUserName!=\"\" or mail.sbDeptName!=\"\"'>" +
            "  <if test='mail.opUserName!=\"\"' > " +
            "   and OpUserName =#{mail.opUserName} " +
            "  </if>" +
            "  <if test='mail.sbDeptName!=\"\"' >" +
            "   and  OpDeptName =#{mail.sbDeptName}" +
            "  </if>" +
            " </if>" +
            " order by m1.WfId desc,m1.LogId desc" +
            "</script>" })
    List<MailWorkFormLogExcel> mailLogListExport(@Param("deptNo")String deptNo,@Param("mail") QueryConditionVo queryConditionVo);

    /**
     * 查询下级单位信件工单
     * @param queryConditionVo
     * @return
     */
    @Select("SELECT t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel," +
            " t3.SpareString,t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc," +
            " t4.JudgeC," +
            " SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit," +
            " BackTime,BackState,BackRemark" +
            " FROM mWfSendBack t1 INNER JOIN mMailWorkForm t2 on t1.WfId=t2.PkId" +
            " inner join mMailGetDeal t3 on t2.OriginFK=t3.PkId left join mWfJudge t4 on t4.WfId = t1.WfId" +
            " WHERE t1.SDeptID=#{sDeptId} AND SendState IN(21,22,23,40) AND t1.IsDel=0 AND t2.IsDel=0 " +
            "[[and WorkFormNO = #{mail.workFormNO}]] [[and WsCode = #{mail.wsCode}]]" +
            "[[and convert(varchar(10),t2.IptTime,120) &gt;= #{mail.beginTime}]] [[and convert(varchar(10),t2.IptTime,120) &lt;= #{mail.endTime}]] " +
            "[[and t2.DCntACode = #{mail.dcntAcode}]] " +
            "[[and FromTopic =#{mail.fromTopic}]] [[and sdeptName =#{mail.sdeptName}]]")
    List<Map> subDeptMailWFList(@Param("sDeptId") String sDeptId, @Param("mail") QueryConditionVo queryConditionVo);

    @Select("QueryReportSql#telWFDeptList")
    @Lang(GroovyLangDriver.class)
    @Options(statementType = StatementType.PREPARED)
    List<Map> telWFDeptListJson(Map paramMap);

    @Select("SELECT JudgeC FROM ( " +
            "SELECT ROW_NUMBER()over(order by pkid desc) AS groupIndex,* from tWfJudge where WfId = #{WfId} " +
            ") AS res WHERE res.groupIndex = 1 ")
    Map<String,Object> findTelJudgeByWfId(@Param("WfId") String wfId);

    /**
     * 查询电话操作记录列表
     * @param queryConditionVo
     * @return
     */
    @Select({"<script>" +
            "SELECT WfId,LogId AS myPkVal,   LogTime AS opTime, SpareString, FlagString, OpDeptNo, OpDeptName, OpUserName, OpAction, OpDesc, WsOpen, WsDesc, CStepId, NStepId " +
            "FROM tWorkFormLog m1 " +
            "WHERE " +
            " IsDel = 0 and OpDeptNo=#{deptNo} and OpUserName is not null " +
            " and (LogTime between #{tel.beginTime} and  #{tel.endTime}) " +
            " <if test='tel.opUserName!=\"\" or tel.sbDeptName!=\"\"'>" +
            "  <if test='tel.opUserName!=\"\"' > " +
            "   and OpUserName =#{tel.opUserName} " +
            "  </if>" +
            "  <if test='tel.sbDeptName!=\"\"' >" +
            "   and  OpDeptName =#{tel.sbDeptName}" +
            "  </if>" +
            " </if>" +
            "</script>"})
    List<Map> telLogList(@Param("deptNo")String deptNo,@Param("tel") QueryConditionVo queryConditionVo);

    /**
     * 电话操作记录数据导出
     * @param queryConditionVo
     * @return
     */
    @Select({"<script>" +
            "SELECT WfId,LogId AS myPkVal,  LogTime AS opTime, SpareString, FlagString, OpDeptNo, OpDeptName, OpUserName, OpAction, OpDesc, WsOpen, WsDesc, CStepId, NStepId " +
            "FROM tWorkFormLog m1 " +
            "WHERE " +
            " IsDel = 0 and OpDeptNo=#{deptNo} and OpUserName is not null " +
            " and (LogTime between #{tel.beginTime} and  #{tel.endTime}) " +
            " <if test='tel.opUserName!=\"\" or tel.sbDeptName!=\"\"'>" +
            "  <if test='tel.opUserName!=\"\"' > " +
            "   and OpUserName =#{tel.opUserName} " +
            "  </if>" +
            "  <if test='tel.sbDeptName!=\"\"' >" +
            "   and  OpDeptName =#{tel.sbDeptName}" +
            "  </if>" +
            " </if>" +
            "order by WfId desc,logId desc"+
            "</script>"})
    List<TelWorkFormLogExcel> telLogListExport(@Param("deptNo")String deptNo,@Param("tel") QueryConditionVo queryConditionVo);

    /**
     * @Description:  查询下级单位电话工单
     * 2023/2/6
     * @Param: [sbDeptId, queryConditionVo]
     * @return: java.util.List<com.xhh.nphm.system.domain.UnsatisfyTelExcel>
     */
    @Select("SELECT t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel," +
            " '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DCntAName,t3.DFmClaName,SBDeptName,BackDesc," +
            " t1.SDeptName,t1.SeqName,t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit," +
            " t4.JudgeC JudgeC," +
            " BackTime,BackState AS BackState,BackRemark" +
            " FROM tWfSendBack t1 inner join tWorkFormBase t2 on t1.WfId=t2.PkId" +
            " inner join tTelGetDeal t3 on t2.GetDealPK=t3.PkId left join tWfJudge t4 on t4.WfId = t1.WfId" +
            " WHERE t1.SDeptID=#{sDeptId} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 and t2.IsDel=0" +
            "[[and t2.workFormNO = #{tel.workFormNO}]] [[and t2.WsCode = #{tel.wsCode}]]" +
            "[[and convert(varchar(10),t2.IptTime,120) &gt;= #{tel.beginTime}]] [[and convert(varchar(10),t2.IptTime,120) &lt;= #{tel.endTime}]] " +
            "[[and t2.DCntACode = #{tel.dcntAcode}]] " +
            "[[and t2.WsTopic =#{tel.fromTopic}]] [[and t1.sdeptName =#{tel.sdeptName}]]")
    List<Map> subDeptTelWFList(@Param("sDeptId") String sDeptId, @Param("tel") QueryConditionVo queryConditionVo);


    @Select("select * from bUserDept where isDel=0 and useState=1 and noUnit=#{deptNo}")
    Dept findDeptById(String deptNo);

    /**
     * 查询同级下级部门名称及其部门成员个数
     * @param deptId
     * @return
     */
    @Select(" with deptTree as (select noUnit,deptShort,OrdNum from bUserDept where pno=#{deptId} and isDel=0 and useState=1 " +
            " union all select a.noUnit,a.deptShort,a.OrdNum from bUserDept a, deptTree b where a.pno = b.NoUnit and a.isDel=0 and a.useState=1) " +
            " select d.deptShort deptName,count(u.user_name) userNum,MAX(u.login_date) recentDate " +
            " from deptTree d inner join tm_sys_user u on u.dept_id = d.noUnit " +
            " GROUP BY d.deptShort,OrdNum ")
    List<Map> findDeptNameOrUserNum(@Param("deptId")String deptId);

    @Select("exec tWfNOSubBackState @wfId=#{wfId},@curSendId=#{sendId}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map<String, Object>> proTsubBackState(@Param("wfId")Integer wfId,@Param("sendId")Integer sendId);

    @Select("exec mWfNOSubBackState @wfId=#{wfId},@curSendId=#{sendId}")
    @Options(statementType = StatementType.CALLABLE)
    List<Map<String, Object>> proMsubBackState(@Param("wfId")Integer wfId,@Param("sendId")Integer sendId);

    /**
     * 电话催办件
     * @param deptId 办理部门
     * @return
     */
    @Select(" select count(1) from tWfSendBack " +
            " where isUrge=1 and SBDeptID=#{deptId} and SendState in (10,21,22,23) and BackState in (0,10,12) " +
            " and dbo.frTelWFStep(WfId)>399 and IsDel=0 ")
    int supUrgeTelOrder(String deptId);

    /**
     * 信件催办件
     * @param deptId 办理部门
     * @return
     */
    @Select(" select count(1) from mWfSendBack " +
            " where isUrge=1 and SBDeptID=#{deptId} and SendState in (10,21,22,23) and BackState in (0,10,12) " +
            " and dbo.frMailWFStep(WfId)>399 and IsDel=0 ")
    int supUrgeMailOrder(String deptId);

    @Select("QueryReportSql#mailWFDeptListTotal")
    @Lang(GroovyLangDriver.class)
    long mailWFDeptListJson_COUNT(Map<String, Object> multiTypeParamListToMap);

    @Select("QueryReportSql#telWFDeptListTotal")
    @Lang(GroovyLangDriver.class)
    long telWFDeptListJson_COUNT(Map<String, Object> multiTypeParamListToMap);

    @Select("SELECT noUnit FROM bUserDept WHERE areaNo=#{fpArea} AND LEN(noUnit)=LEN(#{deptNo}) AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<String> findFlatDeptId(@Param("fpArea") String fpArea, @Param("deptNo") String deptNo);

    @Select("SELECT noUnit FROM bUserDept WHERE noParent=#{fpParent} AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<String> findSonDeptId(@Param("fpParent") String fpParent);

    @Select("select CodePK,LayerNum,SortName,CodeParent,EndFlag from bsort where sortName = #{sortName} and layerNum = #{level} and isDel = 0 and useFlag = 1 ")
    List<BSort> findBSortNoByName(@Param("sortName") String sortName, @Param("level") int level);

    @Select("select CodePK,LayerNum,SortName,CodeParent,EndFlag from bsort where sortName = #{sortName} and codeParent = #{parentNo} and isDel = 0 and useFlag = 1 ")
    BSort selectBSortNoByParent(@Param("sortName") String sortName, @Param("parentNo") String parentNo);

    /**
     * 查询直派部门
     * 区(市)县
     *
     * @param fpParent
     * @return
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noParent in (SELECT noUnit FROM BuserDept WHERE NoParent=#{fpParent} and useState=1 AND isDel=0) AND useState=1 AND isDel=0 ORDER BY ordNum")
    List<Map> queryDirectDept(@Param("fpParent") String fpParent);

    /**
     * 查询直派部门的上级部门
     */
    @Select("SELECT noUnit,deptShort FROM bUserDept WHERE noUnit = (select noparent from bUserDept where noUnit=#{sdeptNo} and useState=1 and isDel=0 ) and useState=1 AND isDel=0")
    Map<String,Object> queryDirectParentDept(String sdeptNo);
}
